package demo04;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import domain.Category;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.ArrayListHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;


/**
 * 专门用来执行数据库的查询操作
 * 		1.QueryRunner(DataSource)
 * 		2.query(String sql, ResultSetHandler<T> rsh, Object... params);//主要执行查询
 *
 * 		ResultSetHandler:结果集处理类
 * 			我们发现ResultSetHandler是一个接口,
 * 			而框架中给我们定义了一堆实现类
 * 			ArrayHandler:
 * 			ArrayListHandler
 * 			**BeanHandler
 * 			**BeanListHandler
 * 			ColumnListHandler
 * 			KeyedHandler
 * 			MapHandler
 * 			MapListHandler
 * 			**ScalarHandler
 *
 *
 *
 * @author yingpeng
 *
 */
public class DBUtilsDemo02 {
	public static void main(String[] args) throws SQLException {
		demo01();
//		demo07();
		demo08();
	}
	//ArrayHandler处理类的使用
	public static void demo01() throws SQLException{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource());
		//2.执行查询
		String sql = "select * from category";
		Object[] objs = qr.query(sql, new ArrayHandler());
		//3.打印
		for (Object obj : objs) {
			System.out.println(obj);
		}
	}

	//ArrayListHandler处理类的使用
	public static void demo02() throws SQLException{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource());
		//2.执行查询
		String sql = "select * from category";
		List<Object[]>  list = qr.query(sql, new ArrayListHandler());
		//3.打印
		for (Object[] objects : list) {
			System.out.println(objects[0]+"\t"+objects[1]);
		}
	}
	//BeanHandler处理类的使用
	public static void demo03() throws SQLException{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource());
		//2.执行查询
		String sql = "select * from category";
		Category c = qr.query(sql, new BeanHandler<Category>(Category.class));
		//3
		System.out.println(c);
	}
	//BeanListHandler处理类的使用
	public static void demo04() throws SQLException{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource());
		//2.执行查询
		String sql = "select * from category";
		List<Category> list = qr.query(sql, new BeanListHandler<Category>(Category.class));
		//3
		for (Category category : list) {
			System.out.println(category);
		}
	}

	//ColumnListHandler处理类的使用
	public static void demo05() throws SQLException{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource());
		//2.执行查询
		String sql = "select * from category";
		List<Object> ids = qr.query(sql, new ColumnListHandler<Object>("cname"));
		//3
		System.out.println(ids);
	}

	//MapHandler处理类的使用
	public static void demo06() throws SQLException{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource());
		//2.执行查询
		String sql = "select * from category";
		Map<String, Object> map = qr.query(sql,new MapHandler());
		//3
		System.out.println(map);
	}
	//MapListHandler处理类的使用
	public static void demo07() throws SQLException{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource());
		//2.执行查询
		String sql = "select * from category";
		List<Map<String, Object>> maps = qr.query(sql,new MapListHandler());
		//3
		System.out.println(maps);
	}

	//ScalarHandler处理类的使用
	public static void demo08() throws SQLException{
		//1.创建QueryRunner对象
		QueryRunner qr = new QueryRunner(C3P0Utils02.getDataSource());
		//2.执行查询
		String sql = "select count(*) from category";
		Object count = qr.query(sql,new ScalarHandler<Object>());
		//3
		System.out.println(count);
	}
}
